package com.ht.pms.core.dao.impl;

import com.ht.pms.core.common.constant.TemplateEnum;
import com.ht.pms.core.dao.SalarySheetManagementDao;
import com.ht.pms.core.dto.DeleteSalaryReq;
import com.ht.pms.core.dto.Params;
import com.ht.pms.core.dto.UpdateSalaryReq;
import com.ht.pms.core.dto.dict.DictParamsDto;
import com.ht.pms.core.dto.tempalte.Template;
import com.ht.pms.core.dto.upload.UpLoadTemp;
import com.ht.pms.core.dto.upload.UploadFile;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Map;

/**
 * @author:喻尊龙
 * @date: 2018/4/13
 */
@Service
public class SalarySheetManagementDaoImpl implements SalarySheetManagementDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Map<String, Object>> getSalaryColum(String templateCode) {
        String sql = "SELECT t2.* FROM t_template_detail t1 " +
                "INNER JOIN t_dict_params t2 ON t1.dict_code = t2.dict_code " +
                "WHERE t1.template_code = '" + templateCode + "' ORDER BY t1.sort ";
        System.out.println("======>>sql:" + sql);
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> getSalaryTableName(String templateCode) {
        String sql = "SELECT t2.dict_table_name as tableName FROM t_template_detail t1 " +
                "INNER JOIN t_dict_params t2 ON t1.dict_code = t2.dict_code " +
                "WHERE t1.template_code = '" + templateCode + "'" +
                "GROUP BY t2.dict_table_name " +
                "HAVING tableName is NOT NULL";
        System.out.println("======>>sql:" + sql);
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<Map<String, Object>> getData(String sql) {
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public Map<String, Object> getCount(String sql) {
        return jdbcTemplate.queryForMap(sql);
    }

    /**
     * 删除数据
     *
     * @param req
     */
    @Override
    public void deleteSalaryData(DeleteSalaryReq req) {
        //查询需要删除的表
        List<Map<String, Object>> maps = getSalaryColum(req.getTemplateCode());
        for (Map<String, Object> tables : maps) {
            String tableName = tables.get("dict_table_name") + "";
            for (Params params : req.getParams()) {
                String jobCode = params.getJob_code();
                String fileId = params.getFile_id();
                if (StringUtils.isNotBlank(jobCode) && StringUtils.isNotBlank(fileId)) {
                    String sql = "DELETE FROM " + tableName + " WHERE job_code='" + jobCode + "' AND file_id='" + fileId + "'";
                    jdbcTemplate.update(sql);
                }
            }
        }
    }

    /**
     * 编辑更新
     *
     * @param req
     */
    @Override
    public int updateSalaryData(UpdateSalaryReq req) {

        String sql = "UPDATE " + req.getTableName() + "  set " + req.getFieldName() + "='" + req.getValue() + "'  WHERE job_code='" + req.getJobCode() + "' and file_id='" + req.getFileId() + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public DictParamsDto getDictParamsDto(String field) {
        String sql = "select * from t_dict_params where dict_code='" + field + "'";
        return (DictParamsDto) jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(DictParamsDto.class));
    }

    @Override
    public List<UploadFile> getUploadFiles(String templateCode, String time) {
        String sql = "SELECT * from t_upload_file t WHERE 1=1 ";
        if (StringUtils.isNotBlank(templateCode)) {
            sql = sql + " AND t.template_code='" + templateCode + "'";
        }
        if (StringUtils.isNotBlank(time)) {
            sql = sql + " AND t.time='" + time + "' ";
        }
        sql = sql + " AND t.is_del=0 AND t.state=1 ORDER BY t.create_time DESC ";

        return jdbcTemplate.query(sql, new BeanPropertyRowMapper(UploadFile.class));
    }

    @Override
    public UpLoadTemp getUpLoadTemp(String templateCode, String time) {
        String sql = "SELECT * from t_upload_temp t1 WHERE t1.template_code='" + templateCode + "' and t1.time='" + time + "' limit 0,1";
        UpLoadTemp temp = null;
        try {
            temp = (UpLoadTemp) jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(UpLoadTemp.class));
        } catch (Exception e) {

        }

        return temp;
    }

    @Override
    public int updataUploadFileLogState(String fileId, String batchCode, String dictCode, String jobCode) {
        String sql = "UPDATE t_upload_file_log l set l.is_repair=1 " +
                "WHERE l.file_id='" + fileId + "' and l.batch_code='" + batchCode + "' and l.job_code='" + jobCode + "' and l.dict_code='" + dictCode + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public int queryNotRepairLogCount(String fileId) {
        String sql = "SELECT count(*) as count FROM t_upload_file_log l WHERE l.file_id='" + fileId + "' " +
                "and l.is_repair=0 and l.version=(SELECT max(version) " +
                "from t_upload_file_log WHERE file_id='" + fileId + "')";
        Map<String, Object> map = jdbcTemplate.queryForMap(sql);
        int count = 0;
        if (map != null) {
            count = Integer.parseInt(map.get("count") + "");
        }
        return count;
    }

    @Override
    public int updataBase(String batchCode, String fileId) {
        String sql = "UPDATE t_dict_record_base t set t.in_state=1 WHERE t.batch_code='" + batchCode + "' and t.file_id='" + fileId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public UploadFile getUploadFile(String templateCode, String time) {
        String sql = "SELECT * from t_upload_file t1 WHERE t1.template_code='" + templateCode + "' and t1.time='" + time + "' limit 0,1";
        UploadFile uploadFile = null;
        try {
            uploadFile = (UploadFile) jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(UploadFile.class));
        } catch (Exception e) {

        }

        return uploadFile;
    }

    @Override
    public int updateFinalState(String batchCode, String templateCode) {
        //考勤
        String sql = "UPDATE t_upload_file SET final_state=1 WHERE batch_code='" + batchCode + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public UploadFile getUploadFileByBatchCode(String batchCode) {
        String sql = "SELECT * FROM t_upload_file WHERE batch_code='" + batchCode + "' LIMIT 0,1";
        UploadFile uploadFile = null;
        try {
            uploadFile = (UploadFile) jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper(UploadFile.class));
        } catch (Exception e) {

        }

        return uploadFile;
    }

    @Override
    public List<Template> findTemp(String tempCode) {
        List<Template> list = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select t.template_code,t.template_type from t_template t where t.template_code='" + tempCode + "'");

        try {
            list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper(Template.class));
        } catch (EmptyResultDataAccessException e) {
        }
        System.out.println(sb.toString());
        return list;
    }

    @Override
    public List<Map<String, Object>> getPmsListSendData(String tableName, String batchCode) {
        List<Map<String, Object>> list = null;
        StringBuilder sb = new StringBuilder();
        sb.append("select t.* from " + tableName + " t INNER JOIN t_upload_file s  ON t.batch_code = s.batch_code AND t.file_id=s.file_id");

        sb.append(" WHERE 1 = 1 ");
        sb.append(" AND s.is_del = 0 AND s.state = 1 AND t.in_state = 1 AND t.audit_state = 1 ");
        sb.append(" AND t.batch_code='" + batchCode + "'");

        try {
            list = jdbcTemplate.queryForList(sb.toString());
        } catch (EmptyResultDataAccessException e) {
        }
        System.out.println(sb.toString());
        return list;
    }

    @Override
    public int countPmsListSendData(String tableName, String batchCode) {
        int count = 0;
        StringBuilder sb = new StringBuilder();
        sb.append("select count(1) from " + tableName + " t INNER JOIN t_upload_file s  ON t.batch_code = s.batch_code AND t.file_id=s.file_id");

        sb.append(" WHERE 1 = 1 ");
        sb.append(" AND s.is_del = 0 AND s.state = 1 AND t.in_state = 1 AND t.audit_state = 1 ");
        sb.append(" AND t.batch_code='" + batchCode + "'");

        try {
            count = jdbcTemplate.queryForObject(sb.toString(), Integer.class);
        } catch (EmptyResultDataAccessException e) {
        }
        System.out.println(sb.toString());
        return count;
    }


}
